We could use a template or define exactly were and in which format to show the data (or charts). There are many customatisations possible with {openxlsx}. If I want to show a table and a chart in a worksheet named summary. I would first create a table and a chart:
tmp1<-tmp %>%filter(vintage %in%c("V2023") & time_period >=2019& Country =="EL"& unit %in%c("PPS_HAB_EU27")) %>%pivot_wider(names_from = time_period,values_from =obs_value) %>%select(geo,NUTS,`2019`, `2020`, `2021`)p<- tmp1 %>%select(geo,NUTS,`2021`) %>%ggplot(aes(x=`2021`, y=reorder(geo,`2021`), fill = NUTS))+geom_col()+theme_minimal()+theme(panel.grid.major.y =element_blank(),legend.position ="none")+scale_fill_manual(values =c("#0E47CB","#FFCC00"))+xlab("")+ylab("")+ggtitle("GDP per capita as % of EU in PPS")
rmarkdown allows creating reproducible documents in various formats and using different languages. We will mainly focus on html files using R. It consist in a YAML section, plain text , code chunks and in-line code.
rmarkdown
The YAML sets some options for the file to be produced. The YAML is very sensible to spaces!
The highest GDP per capita in 2023 measured in euros was registered in `r first` followed by`r second`.
The highest GDP per capita in 2023 measured in euros was registered in LU00 followed by IE05.
rmarkdown
Code chunks is where we will insert the code that produces tables, charts, etc. There is a code chunk (setup) that defines some global parameters.
```{r setup, include = FALSE}## Global optionsknitr::opts_chunk$set( message = FALSE, warning = FALSE, cache = FALSE, fig.height=9, fig.width=12)```
tables
We can create basic tables with knitr::kable().
table<- tmp %>%filter(time_period==2021& NUTS ==2& unit=="EUR_HAB") %>%arrange(desc(obs_value)) %>%select(geo,obs_value) %>%head(5)knitr::kable(table,format="pipe", format.args =list(big.mark =" "),caption="Top 5 Regions in GDP per capita")
Top 5 Regions in GDP per capita
geo
obs_value
LU00
112 800
IE05
101 300
IE06
92 800
DK01
77 100
BE10
73 600
tables
In many cases it is better to create dynamic tables with {DT}.
table<- tmp %>%filter(time_period==2021& NUTS ==2& unit=="PPS_HAB_EU27") %>%filter(obs_value<75) %>%arrange(desc(obs_value)) %>%select(geo,obs_value) DT::datatable(table,caption ="EU Less Developed Regions 2023",filter ="top",class ="stripe hover",extensions ="Buttons",options =list(lengthMenu =list(c(20, -1), c("20", "All")),pageLength =20,dom ="Blfrtip",buttons =c("excel", "csv","pdf") ) )
tables
parametrised reports
We show when looking at the YAML that there some parameters. We can pass externally some parameters and the report will be produced for such parameters. If we create and rmarkdown with the info we showed for Greece, we could create a country parameter and create the report for the country we indicate.